package br.wh.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import br.wh.entities.Chamado;

public class ChamadoDao extends BaseDao {

	DbHelper helper;

	public ChamadoDao(DbHelper helper) {
		this.helper = helper;
	}

	public void inserir(List<Chamado> chamados) throws Exception {
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			db.beginTransaction();
			for (Chamado chamado : chamados) {
				ContentValues values = new ContentValues();
				values.put("NUMOS", chamado.getNumOs());
				values.put("BAIRRO", chamado.getBairro());
				values.put("CIDADE", chamado.getCidade());
				values.put("DESCEQUIPAMENTO", chamado.getDescEquipamento());
				values.put("ENDERECO", chamado.getEndereco());
				values.put("FONE", chamado.getFone());
				values.put("UF", chamado.getUF());
				values.put("CNPJ", chamado.getCNPJ());
				values.put("IE", chamado.getIE());
				values.put("CEP", chamado.getCEP());
				values.put("CONTATO", chamado.getContato());
				values.put("NOME", chamado.getNome());
				values.put("CODSITUACAO", chamado.getCodSituacao());
				values.put("DEFEITORELATADO", chamado.getDefeitoRelatado());
				db.insertOrThrow("TBLCHAMADO", "", values);
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
			db.close();
		}
	}

	public int deletar(long id) throws Exception {
		int itensApagados;
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			db.beginTransaction();
			itensApagados = deletar("_id=?",
					new String[] { String.valueOf(id) });
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
			db.close();
		}
		return itensApagados;
	}

	public int deletar(String where, String[] whereArgs) throws Exception {
		int itensApagados;
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			db.beginTransaction();
			itensApagados = db.delete("TBLCHAMADO", where, whereArgs);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
			db.close();
		}
		return itensApagados;
	}

	/**
	 * Esvazia a tabela
	 * 
	 * @return Quantidade de registros apagados
	 * @throws Exception
	 */
	public int deletarTudo() throws Exception {
		int itensApagados;
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			db.beginTransaction();
			itensApagados = db.delete("TBLCHAMADO", "", new String[] {});
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
			db.close();
		}
		return itensApagados;
	}

	public Chamado buscarOS(String numOs) throws Exception {
		SQLiteDatabase db = helper.getReadableDatabase();
		try {
			Cursor cursor = db.query(true, "TBLCHAMADO", Chamado.COLUNAS,
					"_id=" + numOs, null, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				return cursorToChamado(cursor);
			}
		} finally {
			db.close();
		}
		return null;
	}

	public List<Chamado> listarChamados() throws Exception {
		Cursor c = null;
		List<Chamado> chamados = null;
		SQLiteDatabase db = helper.getReadableDatabase();
		try {

			final String[] COLUNA = new String[] { "NUMOS", "DESCEQUIPAMENTO",
					"NOME", "DEFEITORELATADO", "CODSITUACAO", "DESCRICAO" };

			c = db.rawQuery(
					"SELECT A.NUMOS, A.DESCEQUIPAMENTO, A.NOME, A.DEFEITORELATADO, A.CODSITUACAO, B.DESCRICAO FROM TBLCHAMADO A LEFT JOIN TBLSITUACOES B ON A.CODSITUACAO = B.CODIGO",
					null);

			// c = db.query("TBLCHAMADO", COLUNA, null, null, null, null, null,
			// null);

			chamados = new ArrayList<Chamado>();

			if (c.moveToFirst()) {
				do {
					Chamado ch = new Chamado();
					ch.setNumOs(c.getString(c.getColumnIndexOrThrow("NUMOS")));
					ch.setDescEquipamento(c.getString(c
							.getColumnIndexOrThrow("DESCEQUIPAMENTO")));
					ch.setNome(c.getString(c.getColumnIndexOrThrow("NOME")));
					ch.setDefeitoRelatado(c.getString(c
							.getColumnIndexOrThrow("DEFEITORELATADO")));
					ch.setCodSituacao(c.getString(c
							.getColumnIndexOrThrow("CODSITUACAO")));
					ch.setDescSitucao(c.getString(c
							.getColumnIndexOrThrow("DESCRICAO")));

					chamados.add(ch);
				} while (c.moveToNext());
			}
		} finally {
			db.close();
		}
		return chamados;
	}

	public Chamado buscarOsPorNome(String nome) throws Exception {
		Chamado os = null;
		SQLiteDatabase db = helper.getReadableDatabase();
		try {
			Cursor cursor = db.query("TBLCHAMADO", Chamado.COLUNAS, "nome='"
					+ nome + "'", null, null, null, null);
			if (cursor.moveToNext()) {
				os = cursorToChamado(cursor);
			}
		} finally {
			db.close();
		}
		return os;
	}

	public Cursor query(SQLiteQueryBuilder queryBuilder, String[] projection,
			String selection, String[] selectionArgs, String groupBy,
			String having, String orderBy) throws Exception {
		Cursor c;
		SQLiteDatabase db = helper.getReadableDatabase();
		try {
			c = queryBuilder.query(db, projection, selection, selectionArgs,
					groupBy, having, orderBy);
		} finally {
			db.close();
		}
		return c;
	}

	private Chamado cursorToChamado(Cursor c) throws Exception {
		Chamado chamado = new Chamado();
		chamado.setNumOs(c.getString(c.getColumnIndexOrThrow("NUMOS")));
		chamado.setBairro(c.getString(c.getColumnIndexOrThrow("BAIRRO")));
		chamado.setCidade(c.getString(c.getColumnIndexOrThrow("CIDADE")));
		chamado.setDescEquipamento(c.getString(c
				.getColumnIndexOrThrow("DESCEQUIPAMENTO")));
		chamado.setEndereco(c.getString(c.getColumnIndexOrThrow("ENDERECO")));
		chamado.setFone(c.getString(c.getColumnIndexOrThrow("FONE")));
		chamado.setUF(c.getString(c.getColumnIndexOrThrow("UF")));
		chamado.setCNPJ(c.getString(c.getColumnIndexOrThrow("CNPJ")));
		chamado.setIE(c.getString(c.getColumnIndexOrThrow("IE")));
		chamado.setCEP(c.getString(c.getColumnIndexOrThrow("CEP")));
		chamado.setContato(c.getString(c.getColumnIndexOrThrow("CONTATO")));
		chamado.setNome(c.getString(c.getColumnIndexOrThrow("NOME")));
		chamado.setCodSituacao(c.getString(c
				.getColumnIndexOrThrow("CODSITUACAO")));

		return chamado;
	}
}
